Imports System.Data.OleDb

Public Class frmCliente

    Dim codiceCliente As Integer

    Private Sub cmdPulisci_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdPulisci.Click
        txtCAP.Text = "80055"
        txtCognome.Text = ""
        txtIndirizzo.Text = ""
        txtNome.Text = ""
        txtProvincia.Text = "NA"
        txtTelefono.Text = ""
        txtCitta.Text = "PORTICI"
        codiceCliente = 0
        staBar.Items(1).Text = "Inserimento"
    End Sub

    Private Sub cmdChiudi_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles cmdChiudi.Click
        Me.Close()
    End Sub

    Private Sub cmdCerca_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles cmdCerca.Click
        Dim fCerca As New frmCerca
        Dim cnDb As OleDbConnection
        Dim cmDb As OleDbCommand
        Dim drDb As OleDbDataReader

        sCerca = eCerca.tCliente
        If fCerca.ShowDialog = Windows.Forms.DialogResult.OK Then
            ' carica i dati del cliente
            codiceCliente = fCerca.codiceKey
            cnDb = New OleDbConnection(strConnessioneClienti)
            cnDb.Open()
            cmDb = New OleDbCommand("SELECT * FROM CLIENTI WHERE KEY_ID=" & codiceCliente, cnDb)
            drDb = cmDb.ExecuteReader
            If drDb.Read Then
                txtCAP.Text = drDb.Item("CAP") & ""
                txtCitta.Text = drDb.Item("CITTA") & ""
                txtCognome.Text = drDb.Item("COGNOME") & ""
                txtIndirizzo.Text = drDb.Item("INDIRIZZO") & ""
                txtNome.Text = drDb.Item("NOME") & ""
                txtProvincia.Text = drDb.Item("PROVINCIA") & ""
                txtTelefono.Text = drDb.Item("TELEFONO") & ""
                staBar.Items(1).Text = "Modifica"
            Else
                MsgBox("Errore nel caricamento dei dati", MsgBoxStyle.Critical, "Impossibile aggiornare")
                codiceCliente = 0
            End If
            cnDb.Close()
        End If
        fCerca = Nothing
    End Sub

    Private Sub frmCliente_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Me.Top = 10
        Me.Left = 10

        staBar.Items(1).Text = "Inserimento"
        staBar.Items(3).Text = Date.Today
        codiceCliente = 0
    End Sub

    Private Sub cmdMemorizza_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdMemorizza.Click
        Dim modifica As Boolean
        Dim cnDb As OleDbConnection
        Dim cmDb As OleDbCommand
        Dim strSql As New System.Text.StringBuilder

        If Completo() Then

            If codiceCliente = 0 Then
                codiceCliente = GetNumero("CLIENTI", , False)
                modifica = False
            Else
                modifica = True
            End If

            cnDb = New OleDbConnection(strConnessioneClienti)
            cnDb.Open()
            If modifica Then
                strSql.Append("UPDATE CLIENTI SET NOME='")
                strSql.Append(Apostrophe(txtNome.Text))
                strSql.Append("', COGNOME='")
                strSql.Append(Apostrophe(txtCognome.Text))
                strSql.Append("', INDIRIZZO='")
                strSql.Append(Apostrophe(txtIndirizzo.Text) & "")
                strSql.Append("', CITTA='")
                strSql.Append(Apostrophe(txtCitta.Text) & "")
                strSql.Append("', CAP='")
                strSql.Append(Apostrophe(txtCAP.Text))
                strSql.Append("', PROVINCIA='")
                strSql.Append(Apostrophe(txtProvincia.Text) & "")
                strSql.Append("', TELEFONO='")
                strSql.Append(Apostrophe(txtTelefono.Text) & "' ")
                strSql.Append("WHERE KEY_ID=")
                strSql.Append(codiceCliente)

                cmDb = New OleDbCommand(strSql.ToString, cnDb)
                cmDb.ExecuteNonQuery()
            Else
                strSql.Append("INSERT INTO CLIENTI (KEY_ID,NOME,COGNOME,INDIRIZZO,CITTA,CAP,PROVINCIA,TELEFONO) VALUES (")
                strSql.Append(codiceCliente)
                strSql.Append(",'" & Apostrophe(txtNome.Text) & "'")
                strSql.Append(",'" & Apostrophe(txtCognome.Text) & "'")
                strSql.Append(",'" & Apostrophe(txtIndirizzo.Text) & "'")
                strSql.Append(",'" & Apostrophe(txtCitta.Text) & "'")
                strSql.Append(",'" & Apostrophe(txtCAP.Text) & "'")
                strSql.Append(",'" & Apostrophe(txtProvincia.Text) & "'")
                strSql.Append(",'" & Apostrophe(txtTelefono.Text) & "')")

                cmDb = New OleDbCommand(strSql.ToString, cnDb)
                cmDb.ExecuteNonQuery()
            End If
            cnDb.Close()
            cmdPulisci_Click(sender, e)
            MsgBox("Salvataggio effettuato", MsgBoxStyle.Information, "Salvataggio")
            txtCognome.Focus()
        End If
    End Sub

    Private Function Completo() As Boolean
        Dim strMsg As String = ""

        If txtNome.Text = "" Then
            strMsg = "NOME"
        End If
        If txtCognome.Text = "" Then
            strMsg = "COGNOME"
        End If

        If strMsg <> "" Then
            MsgBox("Campo " & strMsg & " obbligatorio", MsgBoxStyle.Critical, "Impossibile memorizzare")
            Return False
        End If

        Return True
    End Function

    Private Sub cmdElimina_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdElimina.Click
        Dim strExtraTesto As String = ""
        Dim blnHasContratti As Boolean = False

        Try
            If codiceCliente <> 0 Then
                blnHasContratti = hasContratti(codiceCliente)
                If blnHasContratti Then
                    strExtraTesto = vbCrLf & "Verranno cancellati tutti i suoi contratti."
                End If
                If MessageBox.Show("Sicuro di voler eliminare il cliente " & txtCognome.Text & " " & txtNome.Text & " ?" & strExtraTesto, "Eliminazione", MessageBoxButtons.YesNo, MessageBoxIcon.Question, MessageBoxDefaultButton.Button2) = Windows.Forms.DialogResult.Yes Then
                    Using cnConnection As New Data.OleDb.OleDbConnection(strConnessioneClienti)
                        cnConnection.Open()
                        Dim trTrans As OleDb.OleDbTransaction = cnConnection.BeginTransaction(IsolationLevel.ReadCommitted)
                        Using cmCommand As New Data.OleDb.OleDbCommand("Delete From Clienti where key_id=" & codiceCliente, cnConnection, trTrans)
                            cmCommand.ExecuteNonQuery()
                        End Using
                        Using cmCommand As New Data.OleDb.OleDbCommand("Delete From Contratti Where Codice_Cliente=" & codiceCliente, cnConnection, trTrans)
                            cmCommand.ExecuteNonQuery()
                        End Using
                        trTrans.Commit()
                    End Using
                    cmdPulisci_Click(sender, e)
                End If
            Else
                MsgBox("Selezionare il cliente da eliminare", MsgBoxStyle.Critical, "Impossibile eliminare")
            End If
        Catch ex As Exception

        End Try
    End Sub

    Private Function hasContratti(ByVal inCodiceCliente As Integer) As Boolean
        Try
            Using daAdapt As New Data.OleDb.OleDbDataAdapter("Select key_id From CONTRATTI where CODICE_CLIENTE=" & inCodiceCliente, strConnessioneClienti)
                Using dtTable As New DataTable("Contratti")
                    daAdapt.Fill(dtTable)
                    If dtTable.Rows.Count > 0 Then
                        Return True
                    Else
                        Return False
                    End If
                End Using
            End Using
        Catch ex As Exception
            Return False
        End Try
    End Function

    Public Sub New()

        ' This call is required by the Windows Form Designer.
        InitializeComponent()
        Me.MdiParent = frmMain
        ' Add any initialization after the InitializeComponent() call.

    End Sub

    Private Sub txtTelefono_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txtTelefono.KeyPress
        If Not (Char.IsDigit(e.KeyChar)) Or (Char.IsSurrogate(e.KeyChar)) Then
            e.Handled = True
        End If
    End Sub

End Class